<?php
	// 从数据库中获取数据，为了节省内存，不要把数据一次性读到内存，从句柄中一行一行读即可
	//172.31.3.21
	//127.0.0.1
	$pdo =new pdo("mysql:host=127.0.0.1;port=3306;dbname=tddb;","root","tdled2018");
	$pdo->query("set character set 'utf8'");//读库
	$pdo->query("set names 'utf8'");//写库
	//$sql SQL语句
	$sql = 
		"(SELECT
			 YiChangJiTai AS 异常机台
			,YiChangMa AS 异常代码
			,YiChang AS 异常现象
			,YiChangStartTime AS 开始时间
			,YiChangEndTime AS 结束时间
			,CASE WHEN 1 THEN ' ' END AS 处理代码
			,CASE WHEN 1 THEN ' ' END AS 处理方法
			,CASE WHEN 1 THEN 'B' END AS 颜色代码
		FROM
			scanyichang
		LEFT JOIN
			exc
		ON
			YiChangMa = EXC
		WHERE
			DATE(YiChangStartTime)>= CURDATE()-1)
		UNION ALL
		(SELECT
			 DQJT AS 异常机台
			,except_number AS 异常代码
			,except_pho AS 异常现象
			,startime AS 开始时间
			,endtime AS 结束时间
			,fix_code AS 处理代码
			,method AS 处理方法
			,CASE WHEN 1 THEN 'C' END AS 颜色代码
		FROM
			fix
		WHERE
			DATE(startime)>= CURDATE()-1)
		UNION ALL
		(SELECT
			 YiChangJiTai AS 异常机台
			,YiChangMa AS 异常代码
			,YiChang AS 异常现象
			,YiChangStartTime AS 开始时间
			,YiChangEndTime AS 结束时间
			,CASE WHEN BLXM<>'' THEN BLXM ELSE ' ' END AS 处理代码
			,CASE WHEN BLSM<>'' THEN BLSM ELSE ' ' END AS 处理方法
			,CASE WHEN 1 THEN 'R' END AS 颜色代码
		FROM
			qcxunjian
		LEFT JOIN
			exc
		ON
			YiChangMa = EXC
		WHERE
			DATE(YiChangStartTime)>= CURDATE()-1)
		ORDER BY
			 4 DESC
			,5 ASC
		"
	;
	//,CASE WHEN YiChangEndTime IS NULL THEN 'B' ELSE 'G' END AS 颜色代码
	//,CASE WHEN endtime IS NULL THEN 'O' ELSE 'G' END AS 颜色代码
	//,CASE WHEN DATE_FORMAT(startime,'%Y-%m-%d %H:%i:%S') THEN DATE_FORMAT(startime,'%Y-%m-%d %H:%i:%S') ELSE '0000-00-00 00:00:00' END AS 开始时间
	//,CASE WHEN DATE_FORMAT(endtime,'%Y-%m-%d %H:%i:%S') THEN DATE_FORMAT(endtime,'%Y-%m-%d %H:%i:%S') ELSE '0000-00-00 00:00:00' END AS 结束时间
	$res=$pdo->prepare($sql);//准备查询语句 
	$res->execute(); //执行一条查询语句
	$result=$res->fetchAll();//取出所有行
	echo json_encode($result);//转码成json
?>